/**
DROP SEQUENCE SEQ_FOR_DICT_DETAIL;
DROP TRIGGER TRI_FOR_DICT_DETAIL;
DROP SEQUENCE SEQ_FOR_DICT;
DROP TRIGGER TRI_FOR_DICT;
DROP SEQUENCE SEQ_FOR_USER;
DROP TRIGGER TRI_FOR_USER;
DROP SEQUENCE SEQ_FOR_ORGANIZATIONS;
DROP TRIGGER TRI_FOR_ORGANIZATIONS;
DROP SEQUENCE SEQ_FOR_PERSONAL;
DROP TRIGGER TRI_FOR_PERSONAL;
DROP SEQUENCE SEQ_FOR_SALARY_ITEM;
DROP TRIGGER TRI_FOR_SALARY_ITEM;
DROP SEQUENCE SEQ_FOR_SALARY;
DROP TRIGGER TRI_FOR_SALARY;
**/

/**
DROP TABLE SYS_DICT_DETAIL;
DROP TABLE SYS_DICT;
DROP TABLE SYS_USER;
DROP TABLE TB_HR_ORGANIZATIONS;
DROP TABLE TB_HR_PERSONAL;
DROP TABLE TB_HR_SALARY_ITEM;
DROP TABLE TB_HR_SALARY;
**/

/**
 * 数据字典主表
 */
CREATE TABLE SYS_DICT
(
  dict_id  INTEGER not null,
  dict_name   VARCHAR(256) not null,
  dict_type  VARCHAR(64) not null,
  dict_remark VARCHAR(256)
) ;

-- add comments to the table 
comment on table sys_dict
  is '数据字典表';
-- add comments to the columns 
comment on column sys_dict.dict_id
  is '主键';
comment on column sys_dict.dict_name
  is '字典名称';
comment on column sys_dict.dict_type
  is '字典类型';
comment on column sys_dict.dict_remark
  is '字典说明';
  
alter table sys_dict
  add constraint pk_dict_id primary key (dict_id);
alter table sys_dict add constraint UK_SYS_DICT_TYPE unique (DICT_TYPE);

/**
 * 数据字典明细表
 */
create table sys_dict_detail
(
  detail_id  INTEGER not null,
  dict_type      varchar(64),
  detail_name    varchar(256),
  detail_code    varchar(32),
  detail_sort    varchar(32),
  detail_type    varchar(32),
  detail_state   varchar(32),
  detail_content varchar(256),
  detail_remark  varchar(256),
  create_time    varchar(32),
  create_id      integer
);
-- add comments to the table 
comment on table sys_dict_detail
  is '数据字典明细表';
-- add comments to the columns 
comment on column sys_dict_detail.detail_id
  is '主键';
comment on column sys_dict_detail.dict_type
  is '字典类型';
comment on column sys_dict_detail.detail_name
  is '名称';
comment on column sys_dict_detail.detail_code
  is '编号';
comment on column sys_dict_detail.detail_sort
  is '序号';
comment on column sys_dict_detail.detail_type
  is '类型';
comment on column sys_dict_detail.detail_state
  is '状态';
comment on column sys_dict_detail.detail_content
  is '说明';
comment on column sys_dict_detail.detail_remark
  is '备注';
comment on column sys_dict_detail.create_time
  is '创建时间';
comment on column sys_dict_detail.create_id
  is '创建人';
-- create/recreate primary, unique and foreign key constraints 
alter table sys_dict_detail
  add constraint pk_sys_dict_detail_id primary key (detail_id);
alter table sys_dict_detail
  add constraint fk_sys_dict_type foreign key (dict_type)
  references sys_dict (dict_type);
  
/**
 * 用户表
 */
create table sys_user
(
  userid  INTEGER not null,
  username    varchar(32) not null,
  password    varchar(32) not null,
  realname    varchar(32),
  state       varchar(32),
  endtime     varchar(32),
  tel         varchar(32),
  address     varchar(32),
  create_id   integer default 0,
  create_time varchar(32)
);
-- add comments to the columns 
comment on column sys_user.userid
  is '主键';
comment on column sys_user.username
  is '用户名称';
comment on column sys_user.password
  is '用户密码';
comment on column sys_user.realname
  is '真实姓名';
comment on column sys_user.state
  is '状态';
comment on column sys_user.endtime
  is '失效日期';
comment on column sys_user.tel
  is '联系电话';
comment on column sys_user.address
  is '联系地址';
comment on column sys_user.create_id
  is '创建者';
comment on column sys_user.create_time
  is '创建时间';
-- create/recreate primary, unique and foreign key constraints 
alter table sys_user
  add constraint pk_sys_user_userid primary key (userid);

--组织机构
-- Create table
create table TB_HR_ORGANIZATIONS
(
  id           INTEGER PRIMARY KEY not null,
  org_name             VARCHAR2(128) not null,
  short_name       VARCHAR2(128) not null,
  parent_id INTEGER default 0,
  sort        INTEGER default 99,
  remark               VARCHAR2(2400),
  create_time       VARCHAR2(32),
  create_id           INTEGER
);
-- Add comments to the table 
comment on table TB_HR_ORGANIZATIONS
  is '组织机构表';
-- Add comments to the columns 
comment on column TB_HR_ORGANIZATIONS.id
  is '主键';
comment on column TB_HR_ORGANIZATIONS.org_name
  is '组织机构名称';
comment on column TB_HR_ORGANIZATIONS.short_name
  is '组织机构简称';
comment on column TB_HR_ORGANIZATIONS.parent_id
  is '上级组织机构ID';
comment on column TB_HR_ORGANIZATIONS.sort
  is '排序号';
comment on column TB_HR_ORGANIZATIONS.remark
  is '备注';
comment on column TB_HR_ORGANIZATIONS.create_time
  is '创建时间';
comment on column TB_HR_ORGANIZATIONS.create_id
  is '创建人';

--用户表
-- Create table
create table TB_HR_PERSONAL
(
  id              INTEGER PRIMARY KEY not null,
  p_no                   VARCHAR2(128),
  p_name                 VARCHAR2(64) not null,
  org_id             INTEGER,
  p_cert                 VARCHAR2(64),
  p_sex                  INTEGER,
  p_birthdate            VARCHAR2(32),
  p_country              INTEGER,
  p_education            INTEGER,
  p_nation               INTEGER,
  p_shool                VARCHAR2(128),
  p_email                VARCHAR2(64),
  p_marry                INTEGER,
  p_phone                VARCHAR2(64),
  p_tel                  VARCHAR2(64),
  salary      INTEGER,
  salary_bank     INTEGER,
  salary_card  VARCHAR2(64),
  remark                 VARCHAR2(1024),
  create_time               VARCHAR2(32),
  create_id             INTEGER
);
-- Add comments to the table 
comment on table TB_HR_PERSONAL
  is '员工信息表';
-- Add comments to the columns 
comment on column TB_HR_PERSONAL.id
  is '主键';
comment on column TB_HR_PERSONAL.p_no
  is '员工编号';
comment on column TB_HR_PERSONAL.p_name
  is '名称';
comment on column TB_HR_PERSONAL.org_id
  is '组织机构ID';
comment on column TB_HR_PERSONAL.p_cert
  is '证件号码';
comment on column TB_HR_PERSONAL.p_sex
  is '性别 - 1 男 2 女';
comment on column TB_HR_PERSONAL.p_birthdate
  is '生日';
comment on column TB_HR_PERSONAL.p_country
  is '国家 - 数据字段';
comment on column TB_HR_PERSONAL.p_education
  is '学历 - 数据字段';
comment on column TB_HR_PERSONAL.p_nation
  is '民族 - 数据字段';
comment on column TB_HR_PERSONAL.p_shool
  is '毕业学校';
comment on column TB_HR_PERSONAL.p_email
  is 'email';
comment on column TB_HR_PERSONAL.p_marry
  is '是否已婚 - 1 是 2 否';
comment on column TB_HR_PERSONAL.p_phone
  is '手机号';
comment on column TB_HR_PERSONAL.p_tel
  is '电话';
comment on column TB_HR_PERSONAL.salary
  is '基本工资';
comment on column TB_HR_PERSONAL.salary_bank
  is '工资银行';
comment on column TB_HR_PERSONAL.salary_card
  is '工资卡号';
comment on column TB_HR_PERSONAL.remark
  is '备注';
comment on column TB_HR_PERSONAL.create_time
  is '创建时间';
comment on column TB_HR_PERSONAL.create_id
  is '创建人';

--薪酬项目
-- Create table
create table TB_HR_SALARY_ITEM
(
  id       INTEGER PRIMARY KEY not null,
  item_name       VARCHAR2(64) not null,
  item_short_name VARCHAR2(64) not null,
  item_no       INTEGER not null,
  item_type       INTEGER not null,
  org_id       INTEGER,
  sort  INTEGER default 99,
  is_plus  INTEGER default 1,
  is_tax  INTEGER default 1,
  is_show  INTEGER default 1,
  remark     VARCHAR2(4000),
  create_time       VARCHAR2(32),
  create_id           INTEGER
);
-- Add comments to the table 
comment on table TB_HR_SALARY_ITEM
  is '工资项目表';
-- Add comments to the columns 
comment on column TB_HR_SALARY_ITEM.id
  is '主键';
comment on column TB_HR_SALARY_ITEM.item_name
  is '名称';
comment on column TB_HR_SALARY_ITEM.item_short_name
  is '简称(工资条显示)';
comment on column TB_HR_SALARY_ITEM.item_no
  is '对应编号';
comment on column TB_HR_SALARY_ITEM.item_type
  is '工资项目类型';
comment on column TB_HR_SALARY_ITEM.org_id
  is '组织机构ID';
comment on column TB_HR_SALARY_ITEM.sort
  is '排序号';
comment on column TB_HR_SALARY_ITEM.is_plus
  is '正负 1-正 2-负 3-展示';
comment on column TB_HR_SALARY_ITEM.is_tax
  is '是否计税 1-计税 2-不计税';
comment on column TB_HR_SALARY_ITEM.is_show
  is '是否展示 1-展示 2-不展示 ';
comment on column TB_HR_SALARY_ITEM.remark
  is '备注';
comment on column TB_HR_SALARY_ITEM.create_time
  is '创建时间';
comment on column TB_HR_SALARY_ITEM.create_id
  is '创建人';

--工资表
-- Create table
create table TB_HR_SALARY
(
  id            INTEGER  PRIMARY KEY not null,
  p_id        INTEGER not null,
  salary_month VARCHAR2(32) not null,
  remark     VARCHAR2(4000),
  v1              VARCHAR2(32),
  v2              VARCHAR2(32),
  v3              VARCHAR2(32),
  v4              VARCHAR2(32),
  v5              VARCHAR2(32),
  v6              VARCHAR2(32),
  v7              VARCHAR2(32),
  v8              VARCHAR2(32),
  v9              VARCHAR2(32),
  v10             VARCHAR2(32),
  v11             VARCHAR2(32),
  v12             VARCHAR2(32),
  v13             VARCHAR2(32),
  v14             VARCHAR2(32),
  v15             VARCHAR2(32),
  v16             VARCHAR2(32),
  v17             VARCHAR2(32),
  v18             VARCHAR2(32),
  v19             VARCHAR2(32),
  v20             VARCHAR2(32),
  v21             VARCHAR2(32),
  v22             VARCHAR2(32),
  v23             VARCHAR2(32),
  v24             VARCHAR2(32),
  v25             VARCHAR2(32),
  v26             VARCHAR2(32),
  v27             VARCHAR2(32),
  v28             VARCHAR2(32),
  v29             VARCHAR2(32),
  v30             VARCHAR2(32),
  v31             VARCHAR2(32),
  v32             VARCHAR2(32),
  v33             VARCHAR2(32),
  v34             VARCHAR2(32),
  v35             VARCHAR2(32),
  v36             VARCHAR2(32),
  v37             VARCHAR2(32),
  v38             VARCHAR2(32),
  v39             VARCHAR2(32),
  v40             VARCHAR2(32),
  v41             VARCHAR2(32),
  v42             VARCHAR2(32),
  v43             VARCHAR2(32),
  v44             VARCHAR2(32),
  v45             VARCHAR2(32),
  v46             VARCHAR2(32),
  v47             VARCHAR2(32),
  v48             VARCHAR2(32),
  v49             VARCHAR2(32),
  v50             VARCHAR2(32),
  v51             VARCHAR2(32),
  v52             VARCHAR2(32),
  v53             VARCHAR2(32),
  v54             VARCHAR2(32),
  v55             VARCHAR2(32),
  v56             VARCHAR2(32),
  v57             VARCHAR2(32),
  v58             VARCHAR2(32),
  v59             VARCHAR2(32),
  v60             VARCHAR2(32),
  v61             VARCHAR2(32),
  v62             VARCHAR2(32),
  v63             VARCHAR2(32),
  v64             VARCHAR2(32),
  v65             VARCHAR2(32),
  v66             VARCHAR2(32),
  v67             VARCHAR2(32),
  v68             VARCHAR2(32),
  v69             VARCHAR2(32),
  v70             VARCHAR2(32),
  v71             VARCHAR2(32),
  v72             VARCHAR2(32),
  v73             VARCHAR2(32),
  v74             VARCHAR2(32),
  v75             VARCHAR2(32),
  v76             VARCHAR2(32),
  v77             VARCHAR2(32),
  v78             VARCHAR2(32),
  v79             VARCHAR2(32),
  v80             VARCHAR2(32),
  v81             VARCHAR2(32),
  v82             VARCHAR2(32),
  v83             VARCHAR2(32),
  v84             VARCHAR2(32),
  v85             VARCHAR2(32),
  v86             VARCHAR2(32),
  v87             VARCHAR2(32),
  v88             VARCHAR2(32),
  v89             VARCHAR2(32),
  v90             VARCHAR2(1000),
  v91             VARCHAR2(1000),
  v92             VARCHAR2(1000),
  v93             VARCHAR2(1000),
  v94             VARCHAR2(1000),
  v95             VARCHAR2(1000),
  v96             VARCHAR2(1000),
  v97             VARCHAR2(1000),
  v98             VARCHAR2(1000),
  v99             VARCHAR2(1000),
  v100            VARCHAR2(1000),
  create_time               VARCHAR2(32),
  create_id             INTEGER 
) ;
-- Add comments to the table 
comment on table TB_HR_SALARY
  is '工资明细';
-- Add comments to the columns 
comment on column TB_HR_SALARY.id
  is '主键';
comment on column TB_HR_SALARY.p_id
  is '员工ID';
comment on column TB_HR_SALARY.salary_month
  is '工资月份';
comment on column TB_HR_SALARY.remark
  is '备注';
comment on column TB_HR_SALARY.create_time
  is '创建时间';
comment on column TB_HR_SALARY.create_id
  is '创建人';

-----------------------------------------

--SYS_DICT_DETAIL
CREATE SEQUENCE SEQ_FOR_DICT_DETAIL
MINVALUE 1
INCREMENT BY 1
START WITH 1000
NOMAXVALUE
CACHE 10 
NOORDER 
NOCYCLE
;

CREATE OR REPLACE TRIGGER TRI_FOR_DICT_DETAIL
before insert on SYS_DICT_DETAIL
for each row
 DECLARE
begin
select SEQ_FOR_DICT_DETAIL.nextval into :new.detail_id
from dual;
end;
/
ALTER TRIGGER "TRI_FOR_DICT_DETAIL" ENABLE;

--SYS_DICT
CREATE SEQUENCE SEQ_FOR_DICT
MINVALUE 1
INCREMENT BY 1
START WITH 1000
NOMAXVALUE
CACHE 10 
NOORDER 
NOCYCLE
;

CREATE OR REPLACE TRIGGER TRI_FOR_DICT
before insert on SYS_DICT
for each row
 DECLARE
begin
select SEQ_FOR_DICT.nextval into :new.dict_id
from dual;
end;
/
ALTER TRIGGER "TRI_FOR_DICT" ENABLE;

--SYS_USER
CREATE SEQUENCE SEQ_FOR_USER
MINVALUE 1
INCREMENT BY 1
START WITH 1000
NOMAXVALUE
CACHE 10 
NOORDER 
NOCYCLE
;

CREATE OR REPLACE TRIGGER TRI_FOR_USER
before insert on SYS_USER
for each row
 DECLARE
begin
select SEQ_FOR_USER.nextval into :new.userid
from dual;
end;
/
ALTER TRIGGER "TRI_FOR_USER" ENABLE;

--TB_HR_ORGANIZATIONS
CREATE SEQUENCE SEQ_FOR_ORGANIZATIONS
MINVALUE 1
INCREMENT BY 1
START WITH 1000
NOMAXVALUE
CACHE 10 
NOORDER 
NOCYCLE
;

CREATE OR REPLACE TRIGGER TRI_FOR_ORGANIZATIONS 
before insert on TB_HR_ORGANIZATIONS
for each row
 DECLARE
begin
select SEQ_FOR_ORGANIZATIONS.nextval into :new.id
from dual;
end;
/
ALTER TRIGGER "TRI_FOR_ORGANIZATIONS" ENABLE;

--TB_HR_PERSONAL
CREATE SEQUENCE SEQ_FOR_PERSONAL
MINVALUE 1
INCREMENT BY 1
START WITH 1000
NOMAXVALUE
CACHE 10 
NOORDER 
NOCYCLE
;

CREATE OR REPLACE TRIGGER TRI_FOR_PERSONAL
before insert on TB_HR_PERSONAL
for each row
 DECLARE
begin
select SEQ_FOR_PERSONAL.nextval into :new.id
from dual;
end;
/
ALTER TRIGGER "TRI_FOR_PERSONAL" ENABLE;

--TB_HR_SALARY
CREATE SEQUENCE SEQ_FOR_SALARY
MINVALUE 1
INCREMENT BY 1
START WITH 1000
NOMAXVALUE
CACHE 10 
NOORDER 
NOCYCLE
;

CREATE OR REPLACE TRIGGER TRI_FOR_SALARY
before insert on TB_HR_SALARY
for each row
 DECLARE
begin
select SEQ_FOR_SALARY.nextval into :new.id
from dual;
end;
/
ALTER TRIGGER "TRI_FOR_SALARY" ENABLE;

--TB_HR_SALARY_ITEM
CREATE SEQUENCE SEQ_FOR_SALARY_ITEM
MINVALUE 1
INCREMENT BY 1
START WITH 1000
NOMAXVALUE
CACHE 10 
NOORDER 
NOCYCLE
;

CREATE OR REPLACE TRIGGER TRI_FOR_SALARY_ITEM
before insert on TB_HR_SALARY_ITEM
for each row
 DECLARE
begin
select SEQ_FOR_SALARY_ITEM.nextval into :new.id
from dual;
end;
/
ALTER TRIGGER "TRI_FOR_SALARY_ITEM" ENABLE;